import { ALL, Body, Inject, Post, Provide } from '@midwayjs/decorator';
import { CoolController, BaseController, BaseService } from 'midwayjs-cool-core';
import { Context } from 'vm';
import { studentEntity } from '../../entity/student'
import { StudentService } from '../../service/student';
// import { financeEntity } from '../../../finance/entity/finance';
/**
 * 描述
 */
@Provide()
@CoolController({
  api: ['add', 'delete', 'update', 'info', 'list', 'page'],
  entity: studentEntity,
  service: StudentService,
  pageQueryOp: {
    keyWordLikeFields: ["name", "student_id"],
    // 让type字段支持筛选
    fieldEq: ['status'],
    // // 指定返回字段
    // select: ['a.*'],
    // // 关联表用户表
    // leftJoin: [{
    // entity: studentClassDetailEntity,
    //   alias: 'b',
    //   condition: 'a.id = b.student',
    // }],
    where: async (ctx: Context) => {
      return [
        ['a.isDelete = :isDelete', { isDelete: false }]
      ]
    }
  },
  listQueryOp: {
    // 让type字段支持筛选
    fieldEq: ['id'],
    where: async (ctx: Context) => {
      return [
        ['a.isDelete = :isDelete', { isDelete: false }]
      ]
    },
  }
})
export class studentAdminController extends BaseController {
  @Inject()
  service: BaseService
  /**
  * 自定义接口，通过课程类型获取该课程类型下所有的课程体系，然后返回
  * @param query
  */
  @Post('/leaveStudent')
  async courseList(@Body(ALL) queryObject: any) {
    console.log(queryObject, 11111111);

    const info = await this.service.nativeQuery(
      `
      select id,name from student where isDelete = false and is_classgroup = true
      `
    )

    return { code: 1000, data: info }
  }

  /**
  * 报表，统计在读学生和未读学生
  * @param query
  */
  @Post('/studentReport')
  async studentReport(@Body(ALL) queryObject: any) {
    console.log(queryObject, 11111111);

    const studying = await this.service.nativeQuery(
      `
       select count(id) as studying from student 
       where status = 2 and isDelete = false
       group by status
       `
    )
    const stopStudying = await this.service.nativeQuery(
      `
      select count(id) as stopStudying from student 
      where status = 1 and isDelete = false
      group by status
      `
    )
    const leaveStudying = await this.service.nativeQuery(
      `
      select count(id) as leaveStudying from student 
      where status = 0 and isDelete = false
      group by status
      `
    )

    return { code: 1000, data: { studying: studying[0], stopStudying: stopStudying[0], leaveStudying: leaveStudying[0] } }
  }

  /**
 * 报表，统计在读学生和未读学生
 * @param query
 */
  @Post('/restAllClass')
  async restAllClass(@Body(ALL) queryObject: any) {
    let year = queryObject.year;
    let both_year = queryObject.both_year;
    console.log(year,"--------------")
    let restClassList = []
    let leave_sum = []
    const all_common_num = await this.service.nativeQuery(
      `
      select sum(common_num) as all_common_num from finance where isDelete = FALSE and into_date BETWEEN "${both_year[0]}" AND "${both_year[1]}"
      `
    )

    const all_into_money = await this.service.nativeQuery(
      `
      select sum(money) as all_into_money from finance where isDelete = FALSE and into_date BETWEEN "${both_year[0]}" AND "${both_year[1]}"
      `
    )

    // const all_common_num = await this.service.nativeQuery(
    //   `
    //   select sum(common_num) as all_common_num from finance where isDelete = FALSE and into_date BETWEEN "${both_year[0]}" AND "${both_year[1]}"
    //   `
    // )

      // 所有停学学生购买总课时
    const all_stop_study_student_num = await this.service.nativeQuery(
      `
      SELECT sum(common_num) as all_stop_study_student_num 
        from finance WHERE student in 
        (SELECT id from student WHERE isDelete = FALSE and status = 0)
      `
    )
    
    
      // 所有停学学生使用总课时
    const use_stop_study_student_num = await this.service.nativeQuery(
      `
      SELECT sum(common_use_num) as use_stop_study_student_num FROM student_class_detail
        WHERE isDelete  =FALSE and isLeave = false and student in 
        (
        SELECT id from student WHERE isDelete = FALSE and status = 0
        )
      `
    )


      // 所有停课学生购买总课时
      const all_stop_course_student_num = await this.service.nativeQuery(
        `
        SELECT sum(common_num) as all_stop_course_student_num 
          from finance WHERE student in 
          (SELECT id from student WHERE isDelete = FALSE and status = 1)
        `
      )
        // 所有停课学生使用总课时
      const use_stop_course_student_num = await this.service.nativeQuery(
        `
      SELECT sum(common_use_num) as use_stop_course_student_num FROM student_class_detail
        WHERE isDelete  =FALSE and isLeave = false and student in 
        (
        SELECT id from student WHERE isDelete = FALSE and status = 1
        )
      `
      )

    

    const all_special_num = await this.service.nativeQuery(
      `
      select sum(special_num) as all_special_num from finance where isDelete = FALSE and into_date BETWEEN "${both_year[0]}" AND "${both_year[1]}"
      `
    )

    const all_common_use_num = await this.service.nativeQuery(
      `
      select sum(a.common_use_num) as all_common_use_num 
      from student_class_detail a
      LEFT JOIN class_teaching b ON b.id = a.class_teaching
      WHERE a.isDelete = FALSE 
      and a.isLeave = FALSE
      and b.class_date BETWEEN "${both_year[0]}" AND "${both_year[1]}"
      `
    )

    const all_special_use_num = await this.service.nativeQuery(
      `
      select sum(a.special_use_num) as all_special_use_num
      from student_class_detail a
      LEFT JOIN class_teaching b ON b.id = a.class_teaching
      WHERE a.isDelete = FALSE 
      and a.isLeave = FALSE
      and b.class_date BETWEEN "${both_year[0]}" AND "${both_year[1]}"
      `
    )

    function formatZero(num, len) {
      if (String(num).length > len) return num;
      return (Array(len).join("0") + num).slice(-len);
    }
    for (let index = 1; index <= 12; index++) {
      const studying = await this.service.nativeQuery(
        `
          select sum(IFNULL(b.common_use_num_sum,0)) as common_use_num_sum,sum(b.special_use_num_sum) as special_use_num_sum from class_teaching a LEFT JOIN 
          (select class_teaching, sum(common_use_num) as common_use_num_sum, sum(special_use_num) as special_use_num_sum from student_class_detail WHERE isDelete = FALSE and isLeave = false group by class_teaching) b on a.id = b.class_teaching
          where a.class_date like "%${year}-${formatZero(index, 2)}%"
          `
      )
      restClassList.push(studying[0].common_use_num_sum)

    }
    for (let index = 1; index <= 12; index++) {
      const leave_num = await this.service.nativeQuery(
        `
        select sum(IFNULL(leave_sum,0)) as leave_sum from class_teaching a LEFT JOIN 
        (select class_teaching,sum(isLeave) as leave_sum from student_class_detail WHERE isDelete = FALSE and isLeave = TRUE GROUP BY class_teaching) b on a.id = b.class_teaching
        where a.class_date like "%${year}-${formatZero(index, 2)}%"
        `
      )
      leave_sum.push(leave_num[0].leave_sum)

    }

    
    const rest_stop_study_student = parseInt(all_stop_study_student_num[0].all_stop_study_student_num)-parseInt(use_stop_study_student_num[0].use_stop_study_student_num)
    const rest_stop_course_student =  parseInt(all_stop_course_student_num[0].all_stop_course_student_num)-parseInt(use_stop_course_student_num[0].use_stop_course_student_num)
    return { code: 1000, data: { 
      studying: restClassList,
      leave_sum:leave_sum,
      all_common_num:all_common_num[0].all_common_num,
      all_into_money:all_into_money[0].all_into_money,
      all_special_num:all_special_num[0].all_special_num,
      all_common_use_num:all_common_use_num[0].all_common_use_num,
      all_special_use_num:all_special_use_num[0].all_special_use_num,
      all_common_leave_num:all_common_num[0].all_common_num-all_common_use_num[0].all_common_use_num,
      all_special_leave_num:all_special_num[0].all_special_num-all_special_use_num[0].all_special_use_num ,
      rest_stop_study_student:rest_stop_study_student,
      rest_stop_course_student:rest_stop_course_student
     }
    }
  }
}
